GO
CREATE DATABASE survey;
GO
USE survey;
CREATE TABLE RecordAnswer (
  RecordID int NOT NULL,
  QuestionID int NOT NULL,
  MultiID int NOT NULL DEFAULT 0,
  OptionID int DEFAULT NULL,
  Answer varchar(512) DEFAULT NULL
) ;

CREATE TABLE RecordInfo (
  RecordID int NOT NULL,
  SurveyID int NOT NULL,
  RespondentID int NOT NULL,
  SubmitDate datetime NOT NULL DEFAULT GETDATE()
) ;
CREATE TABLE Respondent (
  RespondentID int NOT NULL,
  FirstName varchar(64) DEFAULT NULL,
  LastName varchar(64) DEFAULT NULL,
  Email varchar(320) NOT NULL,
  GroupID int DEFAULT NULL,
  Company varchar(64) DEFAULT NULL,
  Position varchar(32) DEFAULT NULL,
  Phone varchar(32) DEFAULT NULL
) ;

CREATE TABLE RespondentGroup (
  GroupID int NOT NULL
) ;

CREATE TABLE SurveyInfo (
  SurveyID int NOT NULL,
  CreatorID int NOT NULL,
  Title varchar(128) NOT NULL,
  StartDate datetime NOT NULL,
  EndDate datetime NOT NULL,
  CreateDate datetime NOT NULL DEFAULT GETDATE(),
  Reminders tinyint NOT NULL
) ;

CREATE TABLE SurveyOption (
  OptionID int NOT NULL,
  QuestionID int NOT NULL
) ;

CREATE TABLE SurveyQuestion (
  QuestionID int NOT NULL,
  SurveyID int NOT NULL,
  QuestionType varchar(16) NOT NULL,
  Content varchar(512) NOT NULL
) ;

CREATE TABLE "User" (
  UserID int NOT NULL,
  Username varchar(64) NOT NULL
) ;

CREATE TABLE GroupAssign (
	GroupID INT(11),
	SurveyID INT(11)
);


ALTER TABLE RecordAnswer
  ADD PRIMARY KEY (RecordID,QuestionID,MultiID);
ALTER TABLE SurveyInfo
  ADD PRIMARY KEY (SurveyID);
ALTER TABLE SurveyOption
  ADD PRIMARY KEY (OptionID);
ALTER TABLE SurveyQuestion
  ADD PRIMARY KEY (QuestionID);
ALTER TABLE RecordInfo
  ADD PRIMARY KEY (RecordID);
ALTER TABLE Respondent
  ADD PRIMARY KEY (RespondentID);
ALTER TABLE RespondentGroup
  ADD PRIMARY KEY (GroupID);
ALTER TABLE GroupAssign
  ADD PRIMARY KEY (GroupID, SurveyID);


ALTER TABLE RecordAnswer
  ADD CONSTRAINT FK_RecordAnswer_RecordInfo FOREIGN KEY (RecordID) REFERENCES RecordInfo (RecordID);
ALTER TABLE RecordAnswer
  ADD CONSTRAINT FK_RecordAnswer_SurveyOption FOREIGN KEY (OptionID) REFERENCES SurveyOption (OptionID);
ALTER TABLE RecordAnswer
  ADD CONSTRAINT FK_RecordAnswer_SurveyQuestion FOREIGN KEY (QuestionID) REFERENCES SurveyQuestion (QuestionID);

ALTER TABLE RecordInfo
  ADD CONSTRAINT FK_SurveyRecordInfo_Respondent FOREIGN KEY (RespondentID) REFERENCES Respondent (RespondentID);
ALTER TABLE RecordInfo
  ADD CONSTRAINT FK_SurveyRecordInfo_SurveyInfo FOREIGN KEY (SurveyID) REFERENCES SurveyInfo (SurveyID);

ALTER TABLE Respondent
  ADD CONSTRAINT FK_Respondent_RespondentGroup FOREIGN KEY (GroupID) REFERENCES RespondentGroup (GroupID);

ALTER TABLE SurveyQuestion
  ADD CONSTRAINT FK_SurveyQuestion_SurveyInfo FOREIGN KEY (SurveyID) REFERENCES SurveyInfo (SurveyID);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
